﻿using BusinessManage.Common;
using CPFrameWork.Global;
using CPFrameWork.Utility;
using CPFrameWork.Utility.DbOper;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NLog;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace BusinessManage.AccountDept
{
    public class AccountSyncController : CPWebApiBaseController
    {
        DbHelper _helper = new DbHelper("CPYKWL", CPAppContext.CurDbType());
        DbHelper _dephelper = new DbHelper("CPOrganIns", CPAppContext.CurDbType());
        private readonly Logger log = LogManager.GetCurrentClassLogger();

        /// <summary>

        /// </summary>
        public string SyncAccountingInfo()
        {
            //CPOrgan数据库参数  YKWL数据库参数
            ArrayList depArrayList = new ArrayList();
            ArrayList ykwlArrayList = new ArrayList();

            // 1、同步区域会计
            // 1）原始表 2）用户表 3）用户角色关系表  4）用户与组织机构关系表 5）删除YKWL库中区域会计与销售部关系
            // 6）部门表CP_Dep中的UserIds、UserNames字段更新  7）角色表CP_Role中的RoleUserIds、RoleUserNames字段更新

            //获取url、token值、时间间隔
            string Url = CPUtils.Configuration.GetSection("AppSettings")["Url"];
            string tokenId = CPUtils.Configuration.GetSection("AppSettings")["TokenId"];
            string roleId = CPUtils.Configuration.GetSection("AppSettings")["RoleId"];
            //获取时间戳
            string currentTimeStamp = TimeStamp.GetTheTimeStamp(true, DateTime.UtcNow);

            //（1-1）获取区域会计信息参数
            SortedDictionary<string, string> sParaTemp = new SortedDictionary<string, string>();
            sParaTemp.Add("shop_id", "3");
            sParaTemp.Add("role_id", "1");
            sParaTemp.Add("timestamp", currentTimeStamp);
            sParaTemp.Add("method", "account.shop.user.apilist");
            sParaTemp.Add("format", "json");
            sParaTemp.Add("sign_type", "MD5");
            sParaTemp.Add("v", "v1");

            //（1-2）调取生产签名的算法  得到sign值
            string signString = "";
            signString = CreateSign.CreateSignString(sParaTemp, tokenId);
            SortedDictionary<string, string> allPara = new SortedDictionary<string, string>();
            allPara = sParaTemp;
            allPara.Add("sign", signString);
            //得到序列化的参数值,拼接成"key1=value1&key2=value2"形式
            string data = wphttppost.datapost(allPara);
            log.Info("开始从接口获取区域会计基本信息，请求参数为：" + data);

            //（1-3）获取接口中返回的数据
            string result = "";
            try
            {                
                result = wphttppost.Post(data, Url, 6);
                //-------从接口获取数据成功，写入日志
                log.Info("调用区域会计基本信息接口成功，获取数据为：" + result);
                
            }
            catch (Exception ex)
            {
                //-------从接口获取数据失败，写入日志
                log.Info("从接口获取区域会计基本信息失败，失败信息为：{0}\n  详细信息为{1}   \n ", ex.Message, ex.StackTrace);
                return "从接口获取区域会计基本信息数据失败 ：" + ex.Message;
               
            }
            
            try
            {
                //（5）解析数据，插入原始表，添加同步时间
                JObject joAccountResult = (JObject)JsonConvert.DeserializeObject(result);
                if (joAccountResult["error"].ToString().Equals(""))
                {
                    JToken accountersResult = joAccountResult["result"];
                    if (accountersResult.Count() != 0)
                    {
                        DateTime sychTime = DateTime.Now;
                        string orgPassword = MD5.Md5Hash("");
                        StringBuilder accounting_String = new StringBuilder();           //同步区域会计原始表
                        StringBuilder accounting_UpdateString = new StringBuilder();     //同步 用户表				
                        StringBuilder userRelation_UpdateString = new StringBuilder();   //同步 用户与角色关系表
                        StringBuilder userDep_UpdateString = new StringBuilder();        //同步 用户与组织机构关系表
                        StringBuilder accountDep_DeleteString = new StringBuilder();     //同步 删除区域会计与销售部关系
                        StringBuilder role_CheckString = new StringBuilder();            //获取角色信息
                        StringBuilder dept_UpdateString = new StringBuilder();           //更新 组织机构基本信息表
                        StringBuilder role_UpdateString = new StringBuilder();           //更新 角色基本信息表

                        accounting_UpdateString.Append("SET IDENTITY_INSERT CP_User ON;");

                        //（1）获取角色表CP_Role中的区域会计的RoleUserIds、RoleUserNames字段更新
                        string strSqlrole = "select * from CP_Role where RoleId=1";
                        string RoleUserIds = "";             //此角色下的用户Ids（多个用逗号间隔）
                        string RoleUserNames = "";           //此角色下的用户名称（多个用逗号间隔）
                        role_CheckString.Append(strSqlrole);
                        DataTable dtRole = _dephelper.ExecuteDataSet(role_CheckString.ToString()).Tables[0];

                        if (dtRole.Rows.Count > 0)
                        {
                            RoleUserIds = dtRole.Rows[0][2].ToString();
                            RoleUserNames = dtRole.Rows[0][3].ToString();
                        }
                        string[] roleIdsArray = RoleUserIds.Split(','); //用户Ids字符串转数组
                        string[] roleNamesArray = RoleUserNames.Split(','); //用户Ids字符串转数组
                        List<string> roleIdsList = new List<string>(roleIdsArray);
                        List<string> roleNamesList = new List<string>(roleNamesArray);

                        //（2）对各表进行操作
                        foreach (JProperty jpAccounterInfo in accountersResult)
                        {
                            JToken accounterInfo = jpAccounterInfo.Value;
                            //同步区域会计至原始表
                            string seller_id = accounterInfo["seller_id"].ToString();
                            string shop_id = accounterInfo["shop_id"].ToString();
                            string seller_type = accounterInfo["seller_type"].ToString();
                            string role_id = accounterInfo["role_id"].ToString();
                            string name = accounterInfo["name"].ToString();
                            string mobile = accounterInfo["mobile"].ToString();
                            string email = accounterInfo["email"].ToString();
                            string auth_type = accounterInfo["auth_type"].ToString();
                            string modified_time = accounterInfo["modified_time"].ToString();
                            string parent_id = accounterInfo["parent_id"].ToString();
                            string branch_code = accounterInfo["branch_code"].ToString();
                            string area = accounterInfo["area"].ToString();
                            string dms_user_id = accounterInfo["dms_user_id"].ToString();
                            string code = accounterInfo["code"].ToString();
                            string avatar = accounterInfo["avatar"].ToString();
                            string branch_id = accounterInfo["branch_id"].ToString();
                            string login_account = accounterInfo["login_account"].ToString();
                            string disabled = accounterInfo["disabled"].ToString();
                            //拼接插入原始表sql
                            accounting_String.Append("insert into O_sysshop_accountAndseller (seller_id,shop_id,seller_type,role_id,name,mobile,email,auth_type,modified_time,parent_id,code,branch_code,branch_id,login_account,disabled,syntime) values(");
                            accounting_String.Append("'" + seller_id + "',");
                            accounting_String.Append("'" + shop_id + "',");
                            accounting_String.Append("'" + seller_type + "',");
                            accounting_String.Append("'" + role_id + "',");
                            accounting_String.Append("'" + name + "',");
                            accounting_String.Append("'" + mobile + "',");
                            accounting_String.Append("'" + email + "',");
                            accounting_String.Append("'" + auth_type + "',");
                            accounting_String.Append("'" + modified_time + "',");
                            accounting_String.Append("'" + parent_id + "',");
                            accounting_String.Append("'" + code + "',");
                            accounting_String.Append("'" + branch_code + "',");
                            accounting_String.Append("'" + branch_id + "',");
                            accounting_String.Append("'" + login_account + "',");
                            accounting_String.Append("'" + disabled + "',");
                            accounting_String.Append("'" + sychTime + "'");
                            accounting_String.Append(");");

                            //拼接sql,更新物流系统用户表、用户和角色关系表、用户和组织机构关系表
                            //用户表，有就更新，没有就插入;用户表 默认密码、IsCanLogin、IsOuterUser=0 
                            accounting_UpdateString.Append("IF EXISTS (select * from CP_User where UserId='" + seller_id + "') update CP_User set LoginName='" + login_account + "',UserPwd='" + orgPassword + "',UserName='" + name + "',IsCanLogin='" + (disabled == "0" ? "1" : "0") + "',MobilePhone='" + mobile + "',IsOutUser='0' where UserId='" + seller_id + "'");
                            accounting_UpdateString.Append(" else insert into CP_User (UserId,LoginName,UserPwd,UserName,IsCanLogin,MobilePhone,IsOutUser) values(");
                            accounting_UpdateString.Append("'" + seller_id + "',");
                            accounting_UpdateString.Append("'" + login_account + "',");
                            accounting_UpdateString.Append("'" + orgPassword + "',");
                            accounting_UpdateString.Append("'" + name + "',");
                            accounting_UpdateString.Append("'" + (disabled == "0" ? "1" : "0") + "',");
                            accounting_UpdateString.Append("'" + mobile + "',");
                            accounting_UpdateString.Append("'0'");
                            accounting_UpdateString.Append(");");
                            //用户和角色关系表，物流系统中，没有就插入
                            userRelation_UpdateString.Append("IF NOT EXISTS (select * from CP_RoleUser where UserId='" + seller_id + "' and  RoleId='" + roleId + "') ");
                            userRelation_UpdateString.Append(" insert into CP_RoleUser (RoleId,UserId) values(");
                            userRelation_UpdateString.Append("'" + roleId + "',");
                            userRelation_UpdateString.Append("'" + seller_id + "'");
                            userRelation_UpdateString.Append(");");
                            //用户和组织机构关系表   修改：先删除此用户的，再插入
                            //userDep_UpdateString.Append("IF NOT EXISTS (select * from CP_DepUser where UserId='" + seller_id + "' and  DepId='" + branch_id + "') ");
                            userDep_UpdateString.Append("delete from CP_DepUser where UserId='" + seller_id + "';");
                            userDep_UpdateString.Append("insert into CP_DepUser (DepId,UserId) values(");
                            userDep_UpdateString.Append("'" + branch_id + "',");
                            userDep_UpdateString.Append("'" + seller_id + "'");
                            userDep_UpdateString.Append(");");
                            //YKWL业务库，删除此区域会计与销售部的关系
                            accountDep_DeleteString.Append("delete from BD_AccountantDepRelation where UserId='" + seller_id + "';");
                            //判断此用户Id是否存在于RoleUserIds中
                            int roleIndex = Array.IndexOf(roleIdsArray, seller_id);

                            if (roleIndex == -1)
                            {
                                roleIdsList.Add(seller_id);
                                roleNamesList.Add(name);
                            }
                            else
                            {
                                roleIdsList[roleIndex] = seller_id;
                                roleNamesList[roleIndex] = name;
                            }

                            //获取此用户所在部门的部门基本信息表CP_Dep中的UserIds、UserNames字段并更新
                            string strSqldept = "select * from CP_Dep where DepId='" + branch_id + "';";
                            string UserIds = "";             //此部门下的用户Ids（多个用逗号间隔）
                            string UserNames = "";           //此部门下的用户名称（多个用逗号间隔）
                            StringBuilder dept_CheckString = new StringBuilder();            //获取部门信息
                            dept_CheckString.Append(strSqldept);
                            DataTable dtDept = _dephelper.ExecuteDataSet(dept_CheckString.ToString()).Tables[0];
                            if (dtDept.Rows.Count > 0)
                            {
                                UserIds = dtDept.Rows[0][14].ToString();
                                UserNames = dtDept.Rows[0][15].ToString();
                            }
                            string[] userIdsArray = UserIds.Split(','); //用户Ids字符串转数组 
                            string[] userNamesArray = UserNames.Split(','); //用户姓名字符串转数组
                            userIdsArray = userIdsArray.Where(s => !string.IsNullOrEmpty(s)).ToArray();
                            userNamesArray = userNamesArray.Where(s => !string.IsNullOrEmpty(s)).ToArray();
                            List<string> userIdsList = new List<string>(userIdsArray);
                            List<string> userNamesList = new List<string>(userNamesArray);
                            int userIndex = Array.IndexOf(userIdsArray, seller_id);

                            if (userIndex == -1)
                            {
                                userIdsList.Add(seller_id);
                                userNamesList.Add(name);
                            }
                            else
                            {
                                if (userNamesList[userIndex] != name)
                                {
                                    userNamesList[userIndex] = name;
                                }
                            }
                            string[] newUserIdsArray = userIdsList.ToArray();
                            string[] newUserNamesArray = userNamesList.ToArray();
                            string newUserIds = string.Join(",", newUserIdsArray);
                            string newUserNames = string.Join(",", newUserNamesArray);
                            dept_UpdateString.Append("Update CP_Dep set UserIds='" + newUserIds + "',UserNames='" + newUserNames + "' where DepId='" + branch_id + "';");
                            //执行sql，更新组织机构基本信息表
                            _dephelper.ExecuteNonQuery(dept_UpdateString.ToString());
                            //记录日志，原UserIds、UserNames

                        }

                        accounting_UpdateString.Append("SET IDENTITY_INSERT CP_User OFF;");
                        role_UpdateString.Append("Update CP_Role set RoleUserIds='" + RoleUserIds + "',RoleUserNames='" + RoleUserNames + "' Where RoleId='1';");


                        //sql执行参数
                        depArrayList.Add(accounting_UpdateString.ToString());    //更新用户表
                        depArrayList.Add(userRelation_UpdateString.ToString());  //更细用户和角色关系表
                        depArrayList.Add(userDep_UpdateString.ToString());       //更细用户和组织机构关系表
                        depArrayList.Add(role_UpdateString.ToString());          //更新角色基本信息表

                        ykwlArrayList.Add(accounting_String.ToString());         //区域会计基本信息原始表
                        ykwlArrayList.Add(accountDep_DeleteString.ToString());   //删除新接口中区域会计与销售部关系数据
                    }
                }
                else {
                    JToken errorResult = joAccountResult["error"];
                    string errorMessage=errorResult["message"].ToString();
                    log.Error("从接口获取区域会计基本信息失败，获取数据为："+result);
                    return "区域会计基本信息数据获取失败，错误信息为：" + errorMessage;
                }
            }
            catch (Exception ex) {
                //-------从接口获取数据失败，写入日志
                log.Error(string.Format("区域会计基本信息数据解析失败，失败信息为：{0}\n  详细信息为{1}   \n 服务返回数据为{2}", ex.Message, ex.StackTrace, result));
                return "区域会计基本信息数据解析失败 ：" + ex.Message;
            }


            //2、同步区域会计的业务范围（与销售部)
            //（2-1）获取区域会计与销售部关系的参数
            SortedDictionary<string, string> rParaTemp = new SortedDictionary<string, string>();
            rParaTemp.Add("shop_id", "3");
            rParaTemp.Add("timestamp", currentTimeStamp);
            rParaTemp.Add("method", "account.shop.user.apisaledeptlist");
            rParaTemp.Add("format", "json");
            rParaTemp.Add("sign_type", "MD5");
            rParaTemp.Add("v", "v1");

            //（2-2）调取生产签名的算法  得到sign值
            string rsignString = "";
            rsignString = CreateSign.CreateSignString(rParaTemp, tokenId);
            SortedDictionary<string, string> rallPara = new SortedDictionary<string, string>();
            rallPara = rParaTemp;
            rallPara.Add("sign", rsignString);
            //得到序列化的参数值,拼接成"key1=value1&key2=value2"形式
            string datapara = wphttppost.datapost(rallPara);

            //（2-3）获取接口中返回的数据
            string resultr = "";
            try
            {
                resultr = wphttppost.Post(datapara, Url, 6);
                //-------从接口获取数据成功，写入日志
                log.Info("从接口获取区域会计与销售部关系成功，获取数据为：" + resultr);

            }
            catch (Exception ex)
            {
                //-------从接口获取数据失败，写入日志
                log.Info("从接口获取区域会计与销售部关系失败，失败信息为：{0}\n  详细信息为{1}   \n ", ex.Message, ex.StackTrace);
                return "从接口获取区域会计与销售部关系失败 ：" + ex.Message;
            }
            try
            {
                JObject jrelationResult = (JObject)JsonConvert.DeserializeObject(resultr);
                if (jrelationResult["error"].ToString().Equals(""))
                {
                    JArray relationList = (JArray)jrelationResult["result"];
                    if (relationList.Count != 0)
                    {
                        StringBuilder relation_String = new StringBuilder();          //原始表  sql语句 
                        StringBuilder relation_UpdateString = new StringBuilder();    //物流系统 更新sql语句 
                                                                                      //relation_UpdateString.Append("delete from BD_AccountantDepRelation where UserId in ("++");");
                        DateTime sychTime = DateTime.Now;

                        //拼接 SQL
                        foreach (var item in relationList)
                        {
                            string id = item["id"].ToString();
                            string branch_code = item["branch_code"].ToString();
                            string branch_id = item["branch_id"].ToString();
                            string seller_id = item["seller_id"].ToString();
                            string parent_code = item["parent_code"].ToString();
                            //拼接插入原始表sql
                            relation_String.Append("insert into O_sysshop_seller_saledept (id,branch_code,branch_id,seller_id,parent_code,syntime) values(");
                            relation_String.Append("'" + id + "',");
                            relation_String.Append("'" + branch_code + "',");
                            relation_String.Append("'" + branch_id + "',");
                            relation_String.Append("'" + seller_id + "',");
                            relation_String.Append("'" + parent_code + "',");
                            relation_String.Append("'" + sychTime + "'");
                            relation_String.Append(");");
                            //拼接更新物流系统区域会与组织机构业务范围表sql，数据表存储是什么结构 deptd是*,*,*？   
                            relation_UpdateString.Append("insert into BD_AccountantDepRelation (UserId,DepId) values(");
                            relation_UpdateString.Append("'" + seller_id + "',");
                            relation_UpdateString.Append("'" + branch_id + "'");
                            relation_UpdateString.Append(");");
                        }

                        //sql执行参数
                        ykwlArrayList.Add(relation_String.ToString());       //区域会计与组织机构关系  原始表
                        ykwlArrayList.Add(relation_UpdateString.ToString());       //物流系统的区域会计与组织机构业务范围表
                    }
                }
                else
                {
                    JToken rerrorResult = jrelationResult["error"];
                    string rerrorMessage = rerrorResult["message"].ToString();
                    log.Error("从接口获取区域会计基本信息失败，获取数据为：" + resultr);
                    return "区域会计基本信息数据获取失败，错误信息为：" + rerrorMessage;
                }
            }
            catch (Exception ex)
            {
                //-------从接口获取数据失败，写入日志
                log.Error(string.Format("区域会计与销售关系数据解析失败，失败信息为：{0}\n  详细信息为{1}   \n 服务返回数据为{2}", ex.Message, ex.StackTrace, resultr));
                return "区域会计与销售关系数据解析失败：" + ex.Message;
            }
            try
            {
                _dephelper.ExecuteNonQuery(depArrayList);
                _helper.ExecuteNonQuery(ykwlArrayList);
                log.Info("区域会计同步成功。");
                return ("同步成功");
            }
            catch (Exception ex)
            {                
                log.Error(string.Format("数据入库时执行失败! 错误信息为：{0}\n 详细信息为:{1} \n ",  ex.Message, ex.StackTrace));
                return ("同步失败，请联系管理员！");
            }           

        }
    }
}